﻿using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SharePoint.WebPartPages;
using Microsoft.SharePoint;

namespace sqlUpdateTableTool
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private string  sqlServer;
        private string database;
        private string username;
        private string password;

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void UpdateTable()
        {
            sqlServer = txtDBServer.Text;
            database = txtDBName.Text;
            username = txtUserName.Text;
            password = txtPassword.Text;

            txtResult.Clear();
            try    
            {
                int rows =0;
                using (SqlConnection conn = getConnection())
                {            

                    string folderPath = string.Empty;

                    using (OpenFileDialog fdb = new OpenFileDialog())
                    {
                        if (fdb.ShowDialog() == DialogResult.OK)
                        {
                            string[] allLines = File.ReadAllLines(fdb.FileName);

                            string[] english = new string[allLines.Length];

                            string[] otherLanguage = new string[allLines.Length];

                            for (int i = 0; i < allLines.Length; i++)
                            {
                                string[] lineSplit = allLines[i].Split(char.Parse(cbbSeperator.SelectedItem.ToString()));

                                english[i] = lineSplit[0];
                                otherLanguage[i] = lineSplit[1];

                                string a = txtSetColl.Text;
                                a = a.Replace("[", "");
                                a = a.Replace("]", "");
                                a = a.Replace("-", "");
                                string b = txtWhereColl.Text;
                                b = b.Replace("[", "");
                                b = b.Replace("]", "");
                                b = b.Replace("-", "");

                                using (SqlCommand cmd = new SqlCommand("UPDATE " + txtTableName.Text + " SET " + txtSetColl.Text + " =@" + a + " WHERE " + txtWhereColl.Text + " =@" + b + "", conn))
                                {
                                    string val1 = "@" + b;
                                    string val1ans =english[i];

                                    string val2 = "@" + a;
                                    string val2ans = otherLanguage[i];

                                    // Create two different encodings.
                                    Encoding ascii = Encoding.UTF8;
                                    Encoding unicode = Encoding.Default;

                                    // Convert the string into a byte array. 
                                    byte[] unicodeBytes = unicode.GetBytes(val2ans);

                                    // Perform the conversion from one encoding to the other. 
                                    byte[] asciiBytes = Encoding.Convert(unicode, ascii, unicodeBytes);

                                    // Convert the new byte[] into a char[] and then into a string. 
                                    char[] asciiChars = new char[ascii.GetCharCount(asciiBytes, 0, asciiBytes.Length)];
                                    ascii.GetChars(asciiBytes, 0, asciiBytes.Length, asciiChars, 0);
                                    val2ans = new string(asciiChars);


                                    SqlParameter p1 = new SqlParameter(@val1, SqlDbType.VarChar);
                                    p1.Value = val1ans;
                                    cmd.Parameters.Add(p1);
                                    SqlParameter p2 = new SqlParameter(@val2, SqlDbType.VarChar);
                                    p2.Value = val2ans;
                                    cmd.Parameters.Add(p2);
                                    rows = cmd.ExecuteNonQuery();
                                    //rows number of record got updated 
                                    txtResult.Text += "ADDED " + val1 + " = " + val1ans + " : " + val2 + " = " + val2ans + Environment.NewLine;  

                                }
                            }

                        }
                    }
                    txtResult.Text += rows + Environment.NewLine;   
                  }
                
            }    
            catch (SqlException ex)   
            {
                if (ex.Message == "Index was outside the bounds of the array")
                {
                    MessageBox.Show("ERROR: Please make sure your file containst no spaces between each line and that it contains two rows");
                }
                else
                {
                    MessageBox.Show(ex.Message);
                    //Log exception      
                    //Display Error message   
                }
            }
        }
        
        /// <summary>
        /// Make the SQL connection to connect to the database
        /// </summary>
        /// <returns></returns>
        public SqlConnection getConnection()
        {
            SqlConnection connection = null;
            try
            {
                string sqlConnectionString = String.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}",
                    sqlServer, database, username, password);
                connection = new SqlConnection(sqlConnectionString);
                connection.Open();

                return connection;
            }
            catch
            {
                if (connection != null)
                    connection.Close();
                throw;
            }

        }
        

        private void button1_Click(object sender, EventArgs e)
        {
            UpdateTable();
        }

        private void cbShowHidePass_CheckedChanged(object sender, EventArgs e)
        {
            if (cbShowHidePass.Checked == true)
            {
                this.txtPassword.UseSystemPasswordChar = false;
            }
            else
            {
                this.txtPassword.UseSystemPasswordChar = true;
            }
        }
    }
}
